IF OBJECT_ID('dbo.RptPCROWeeklyStatisticsReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROWeeklyStatisticsReport
    IF OBJECT_ID('dbo.RptPCROWeeklyStatisticsReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROWeeklyStatisticsReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROWeeklyStatisticsReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPCROWeeklyStatisticsReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 08/20/2008
--
-- Description: Source of Report, "PCROWeeklyStatisticsReport"
--                  In Insight DB Reporting Services
--
-- Report to count Deliverable Activity by Pre-Award Manager
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptPCROWeeklyStatisticsReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 08/20/08    KC        Created
-- 08/28/08    KC        Added MCA-Completed counts
--
-- =======================================================================
CREATE PROCEDURE dbo.RptPCROWeeklyStatisticsReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @IncludeDanaFarber                 VARCHAR(3) = NULL,
     @IncludeUnassigned                 INT=0,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error         INT,
               @RowCount      INT,
               @Institution   VARCHAR(50)
               
     -- Check DanaFarber
     IF UPPER(@IncludeDanaFarber) != 'YES'
          SET @IncludeDanaFarber = NULL

     -- Set a list of Institutions
     IF @IncludeDanaFarber IS NOT NULL
          SET  @Institution = 'BWH,MGH,NWH,SRH,DFCI,DFPCC'
     ELSE
          SET  @Institution = 'BWH,MGH,NWH,SRH'

     -- Have to get a list of all Agreements by CSCRCaseManager, Institution, and 
     -- SubActivity Type, then we'll slice the counts up when we return.
     CREATE TABLE #tt_Counts
          (PreAwardManagerId            INT NOT NULL,
          PreAwardManagerName           VARCHAR(50) NOT NULL,
          DeliverableCategory           VARCHAR(50) NULL,
          DeliverableCount              INT NULL)
          
          
               
     -- Get Raw Counts..
     INSERT    #tt_Counts
               (PreAwardManagerId,  
               PreAwardManagerName, 
               DeliverableCategory,
               DeliverableCount)    
     SELECT    ISNULL(AI.PreAwardManagerId,-1) AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Unassigned') AS PreAwardManagerName,
               CASE WHEN D.REPORTCAT_CODE IN ('REPORTCAT_35','REPORTCAT_30','REPORTCAT_42') AND
                                        D.REPORTSTAT_CODE IN ('REPORTSTAT_61')
                         THEN 'BudgetsPending'
                    WHEN D.REPORTCAT_CODE IN ('REPORTCAT_31') AND
                                        D.REPORTSTAT_CODE IN ('REPORTSTAT_61')
                         THEN 'DevicesPending'
                    WHEN D.REPORTCAT_CODE IN ('REPORTCAT_41') AND
                                        D.REPORTSTAT_CODE IN ('REPORTSTAT_61')
                         THEN 'MCAPending'
                    ELSE 'IGNORE'
               END AS DeliverableCategory,
               COUNT(D.ReportID)
     FROM      dbo.AgrAgreement_Info AI 
               INNER JOIN dbo.vwInfoEdCSCRDeliverablesAll D ON AI.InfoEdPropNumber = D.prop_no
     WHERE     D.ProcessedDate BETWEEN @StartDate and @EndDate AND
               (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institution))) AND
               (@IncludeUnassigned = 1 OR AI.PreAwardManagerId IS NOT NULL) AND
               D.REPORTCAT_CODE IN ('REPORTCAT_35','REPORTCAT_30','REPORTCAT_42','REPORTCAT_31','REPORTCAT_41')
     GROUP BY  ISNULL(AI.PreAwardManagerId,-1),
               ISNULL(AI.PreAwardManagerName,'Unassigned'),
               CASE WHEN D.REPORTCAT_CODE IN ('REPORTCAT_35','REPORTCAT_30','REPORTCAT_42') AND
                                        D.REPORTSTAT_CODE IN ('REPORTSTAT_61')
                         THEN 'BudgetsPending'
                    WHEN D.REPORTCAT_CODE IN ('REPORTCAT_31') AND
                                        D.REPORTSTAT_CODE IN ('REPORTSTAT_61')
                         THEN 'DevicesPending'
                    WHEN D.REPORTCAT_CODE IN ('REPORTCAT_41') AND
                                        D.REPORTSTAT_CODE IN ('REPORTSTAT_61')
                         THEN 'MCAPending'
                    ELSE 'IGNORE'
               END
          
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          DROP TABLE #tt_Counts
          RETURN -1
     END
     
     -- Get MCA Completed by Qualifying types...
     INSERT    #tt_Counts
               (PreAwardManagerId,  
               PreAwardManagerName, 
               DeliverableCategory,
               DeliverableCount)
     SELECT    PreAwardManager,
               PreAWardManagerName,
               DeliverableCategory,
               SUM(DeliverableCount)
     FROM               
               (SELECT   CASE RTRIM(P.QualityStudyStatus)
                              WHEN 'Qualifying' THEN -1 
                              WHEN 'Non Qualifying - Grid' THEN -2
                              WHEN 'Non Qualifying - No Grid' THEN -3
                              WHEN 'No 3rd Party Billing' THEN -4
                              ELSE NULL
                         END AS PreAwardManager,
                         RTRIM(P.QualityStudyStatus) AS PreAwardManagerName,
                         'MCACompleted' AS DeliverableCategory,
                         COUNT(D.ReportID) AS DeliverableCount
               FROM      dbo.AgrAgreement_Info AI 
                         INNER JOIN dbo.vwInfoEdCSCRDeliverablesAll D ON AI.InfoEdPropNumber = D.prop_no
                         INNER JOIN dbo.InfoEdUDFProposal P ON AI.InfoEdPropNumber = P.PropNumber
               WHERE     D.ProcessedDate BETWEEN @StartDate and @EndDate AND
                         (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institution))) AND
                         (@IncludeUnassigned = 1 OR AI.PreAwardManagerId IS NOT NULL) AND
                         D.REPORTCAT_CODE = 'REPORTCAT_41' AND
                         D.REPORTSTAT_CODE IN ('REPORTSTAT_13','REPORTSTAT_61') AND  -- CSCR-Completed
                         RTRIM(P.QualityStudyStatus) IN ('Qualifying','Non Qualifying - Grid','Non Qualifying - No Grid','No 3rd Party Billing')
               GROUP BY  CASE RTRIM(P.QualityStudyStatus)
                          WHEN 'Qualifying' THEN -1 
                          WHEN 'Non Qualifying - Grid' THEN -2
                          WHEN 'Non Qualifying - No Grid' THEN -3
                          WHEN 'No 3rd Party Billing' THEN -4
                          ELSE NULL
                         END,
                         RTRIM(P.QualityStudyStatus)
               -- Seed these counts with zeroes.
               UNION
               SELECT    -1,
                         'Qualifying',
                         'MCACompleted',
                         0
               UNION
               SELECT    -2,
                         'Non-Qualifying Grid',
                         'MCACompleted',
                         0
               UNION
               SELECT    -3,
                         'Non-Qualifying No Grid',
                         'MCACompleted',
                         0
               UNION
               SELECT    -4,
                         'No 3rd Party',
                         'MCACompleted',
                         0         ) TOTALS
     GROUP BY  PreAwardManager,
               PreAWardManagerName,
               DeliverableCategory                      
     
          
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          DROP TABLE #tt_Counts
          RETURN -1
     END

     
     
     -- Result Set
     SELECT    PreAwardManagerId,  
               PreAwardManagerName, 
               SUM(CASE WHEN DeliverableCategory = 'BudgetsPending'
                    THEN DeliverableCount
                    ELSE 0
               END) AS BudgetsPending,
               SUM(CASE WHEN DeliverableCategory = 'DevicesPending'
                    THEN DeliverableCount
                    ELSE 0
               END) AS DevicesPending,
               SUM(CASE WHEN DeliverableCategory = 'MCAPending'
                    THEN DeliverableCount
                    ELSE 0
               END) AS MCAPending,
               SUM(CASE WHEN DeliverableCategory = 'MCACompleted'
                    THEN DeliverableCount
                    ELSE 0
               END) AS MCACompleted
     FROM      #tt_Counts
     WHERE     DeliverableCategory != 'IGNORE'
     GROUP BY  PreAwardManagerId,  
               PreAwardManagerName
     ORDER BY  PreAwardManagerName

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          DROP TABLE #tt_Counts
          RETURN -1
     END
     
     -- Housecleaning
     DROP TABLE #tt_Counts
     
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptPCROWeeklyStatisticsReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROWeeklyStatisticsReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROWeeklyStatisticsReport >>>'
go

GRANT EXECUTE ON dbo.RptPCROWeeklyStatisticsReport TO InsightReport,Insight
go


